<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/xhtml;charset=UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=9"/>
<meta name="generator" content="Doxygen 1.8.20"/>
<meta name="viewport" content="width=device-width, initial-scale=1"/>
<title>libxlsxwriter: Tutorial 2: Adding formatting to the XLSX File</title>
<link href="tabs.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript" src="dynsections.js"></script>
<link href="search/search.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="search/searchdata.js"></script>
<script type="text/javascript" src="search/search.js"></script>
<link href="doxygen.css" rel="stylesheet" type="text/css" />
<link href="customdoxygen.css" rel="stylesheet" type="text/css"/>
</head>
<body>
<div id="top"><!-- do not remove this div, it is closed by doxygen! -->
<div id="titlearea">
<table cellspacing="0" cellpadding="0">
 <tbody>
 <tr style="height: 56px;">
  <td id="projectalign" style="padding-left: 0.5em;">
   <div id="projectname">libxlsxwriter
   </div>
  </td>
 </tr>
 </tbody>
</table>
</div>
<!-- end header part -->
<!-- Generated by Doxygen 1.8.20 -->
<script type="text/javascript">
/* @license magnet:?xt=urn:btih:cf05388f2679ee054f2beb29a391d25f4e673ac3&amp;dn=gpl-2.0.txt GPL-v2 */
var searchBox = new SearchBox("searchBox", "search",false,'Search');
/* @license-end */
</script>
<script type="text/javascript" src="menudata.js"></script>
<script type="text/javascript" src="menu.js"></script>
<script type="text/javascript">
/* @license magnet:?xt=urn:btih:cf05388f2679ee054f2beb29a391d25f4e673ac3&amp;dn=gpl-2.0.txt GPL-v2 */
$(function() {
  initMenu('',true,false,'search.php','Search');
  $(document).ready(function() { init_search(); });
});
/* @license-end */</script>
<div id="main-nav"></div>
<!-- window showing the filter options -->
<div id="MSearchSelectWindow"
     onmouseover="return searchBox.OnSearchSelectShow()"
     onmouseout="return searchBox.OnSearchSelectHide()"
     onkeydown="return searchBox.OnSearchSelectKey(event)">
</div>

<!-- iframe showing the search results (closed by default) -->
<div id="MSearchResultsWindow">
<iframe src="javascript:void(0)" frameborder="0" 
        name="MSearchResults" id="MSearchResults">
</iframe>
</div>

</div><!-- top -->
<div class="PageDoc"><div class="header">
  <div class="headertitle">
<div class="title">Tutorial 2: Adding formatting to the XLSX File </div>  </div>
</div><!--header-->
<div class="contents">
<div class="textblock"><p>Next: <a class="el" href="tutorial03.html">Tutorial 3: Writing different types of data to the XLSX File</a>.</p>
<p>In the previous section we created a simple spreadsheet using C and the <code>libxlsxwriter</code> library.</p>
<p>This converted the required data into an Excel file but it looked a little bare. In order to make the information clearer we would like to add some simple formatting, like this:</p>
<div class="image">
<img src="tutorial02.png" alt=""/>
</div>
<p>The differences here are that we have added <b>Item</b> and <b>Cost</b> column headers in a bold font, we have formatted the currency in the second column and we have made the <b>Total</b> string bold.</p>
<p>To do this we can extend our program as follows:</p>
 <div class="fragment"><div class="line"><span class="preprocessor">#include &quot;xlsxwriter.h&quot;</span></div>
<div class="line"> </div>
<div class="line"><span class="comment">/* Some data we want to write to the worksheet. */</span></div>
<div class="line"><span class="keyword">struct </span>expense {</div>
<div class="line">    <span class="keywordtype">char</span> item[32];</div>
<div class="line">    <span class="keywordtype">int</span>  cost;</div>
<div class="line">};</div>
<div class="line"> </div>
<div class="line"><span class="keyword">struct </span>expense expenses[] = {</div>
<div class="line">    {<span class="stringliteral">&quot;Rent&quot;</span>, 1000},</div>
<div class="line">    {<span class="stringliteral">&quot;Gas&quot;</span>,   100},</div>
<div class="line">    {<span class="stringliteral">&quot;Food&quot;</span>,  300},</div>
<div class="line">    {<span class="stringliteral">&quot;Gym&quot;</span>,    50},</div>
<div class="line">};</div>
<div class="line"> </div>
<div class="line"> </div>
<div class="line"><span class="keywordtype">int</span> main() {</div>
<div class="line"> </div>
<div class="line">    <span class="comment">/* Create a workbook and add a worksheet. */</span></div>
<div class="line">    <a class="code" href="structlxw__workbook.html">lxw_workbook</a>  *workbook  = <a class="code" href="workbook_8h.html#a1cf96608a23ee4eb0e8467c15240d00b">workbook_new</a>(<span class="stringliteral">&quot;tutorial02.xlsx&quot;</span>);</div>
<div class="line">    <a class="code" href="structlxw__worksheet.html">lxw_worksheet</a> *worksheet = <a class="code" href="workbook_8h.html#a81d456b4f65a464e78e4a0030ecc3c2e">workbook_add_worksheet</a>(workbook, NULL);</div>
<div class="line">    <span class="keywordtype">int</span> row = 0;</div>
<div class="line">    <span class="keywordtype">int</span> col = 0;</div>
<div class="line">    <span class="keywordtype">int</span> i;</div>
<div class="line"> </div>
<div class="line">    <span class="comment">/* Add a bold format to use to highlight cells. */</span></div>
<div class="line">    <a class="code" href="structlxw__format.html">lxw_format</a> *bold = <a class="code" href="workbook_8h.html#a279a5d7075d09a3931aae9782afede33">workbook_add_format</a>(workbook);</div>
<div class="line">    <a class="code" href="format_8h.html#a85e1b0baf44b445b65894e48722aec14">format_set_bold</a>(bold);</div>
<div class="line"> </div>
<div class="line">    <span class="comment">/* Add a number format for cells with money. */</span></div>
<div class="line">    <a class="code" href="structlxw__format.html">lxw_format</a> *money = <a class="code" href="workbook_8h.html#a279a5d7075d09a3931aae9782afede33">workbook_add_format</a>(workbook);</div>
<div class="line">    <a class="code" href="format_8h.html#af77bbd0003344cb16d455c7fb709e16c">format_set_num_format</a>(money, <span class="stringliteral">&quot;$#,##0&quot;</span>);</div>
<div class="line"> </div>
<div class="line">    <span class="comment">/* Write some data header. */</span></div>
<div class="line">    <a class="code" href="worksheet_8h.html#ac208046e7a6d12cc87982422efa41b31">worksheet_write_string</a>(worksheet, row, col,     <span class="stringliteral">&quot;Item&quot;</span>, bold);</div>
<div class="line">    <a class="code" href="worksheet_8h.html#ac208046e7a6d12cc87982422efa41b31">worksheet_write_string</a>(worksheet, row, col + 1, <span class="stringliteral">&quot;Cost&quot;</span>, bold);</div>
<div class="line"> </div>
<div class="line">    <span class="comment">/* Iterate over the data and write it out element by element. */</span></div>
<div class="line">    <span class="keywordflow">for</span> (i = 0; i &lt; 4; i++) {</div>
<div class="line">        <span class="comment">/* Write from the first cell below the headers. */</span></div>
<div class="line">        row = i + 1;</div>
<div class="line">        <a class="code" href="worksheet_8h.html#ac208046e7a6d12cc87982422efa41b31">worksheet_write_string</a>(worksheet, row, col,     expenses[i].item, NULL);</div>
<div class="line">        <a class="code" href="worksheet_8h.html#ad9fc47d3beaa2ab4759414e8580c2289">worksheet_write_number</a>(worksheet, row, col + 1, expenses[i].cost, money);</div>
<div class="line">    }</div>
<div class="line"> </div>
<div class="line">    <span class="comment">/* Write a total using a formula. */</span></div>
<div class="line">    <a class="code" href="worksheet_8h.html#ac208046e7a6d12cc87982422efa41b31">worksheet_write_string</a> (worksheet, row + 1, col,     <span class="stringliteral">&quot;Total&quot;</span>,       bold);</div>
<div class="line">    <a class="code" href="worksheet_8h.html#ae57117f04c82bef29805ec3eabc219bb">worksheet_write_formula</a>(worksheet, row + 1, col + 1, <span class="stringliteral">&quot;=SUM(B2:B5)&quot;</span>, money);</div>
<div class="line"> </div>
<div class="line">    <span class="comment">/* Save the workbook and free any allocated memory. */</span></div>
<div class="line">    <span class="keywordflow">return</span> <a class="code" href="workbook_8h.html#ad9e7aeebc0fd43562db5bcd527b2ee5e">workbook_close</a>(workbook);</div>
<div class="line">}</div>
</div><!-- fragment --><p>The main difference between this and the previous program is that we have added two <a class="el" href="format_8h.html">Format</a> objects that we can use to format cells in the spreadsheet.</p>
<p>Format objects represent all of the formatting properties that can be applied to a cell in Excel such as fonts, number formatting, colors and borders. This is explained in more detail in <a class="el" href="format_8h.html">The Format object</a> and <a class="el" href="working_with_formats.html">Working with Formats</a>.</p>
<p>For now we will avoid getting into the details and just use a limited amount of the format functionality to add some simple formatting:</p>
 <div class="fragment"><div class="line">    <span class="comment">/* Add a bold format to use to highlight cells. */</span></div>
<div class="line">    <a class="code" href="structlxw__format.html">lxw_format</a> *bold = <a class="code" href="workbook_8h.html#a279a5d7075d09a3931aae9782afede33">workbook_add_format</a>(workbook);</div>
<div class="line">    <a class="code" href="format_8h.html#a85e1b0baf44b445b65894e48722aec14">format_set_bold</a>(bold);</div>
<div class="line"> </div>
<div class="line">    <span class="comment">/* Add a number format for cells with money. */</span></div>
<div class="line">    <a class="code" href="structlxw__format.html">lxw_format</a> *money = <a class="code" href="workbook_8h.html#a279a5d7075d09a3931aae9782afede33">workbook_add_format</a>(workbook);</div>
<div class="line">    <a class="code" href="format_8h.html#af77bbd0003344cb16d455c7fb709e16c">format_set_num_format</a>(money, <span class="stringliteral">&quot;$#,##0&quot;</span>);</div>
</div><!-- fragment --><p>We can then pass these formats as a parameter to the <code>worksheet_write*()</code> functions to format the data in the cell:</p>
 <div class="fragment"><div class="line">    <a class="code" href="worksheet_8h.html#ac208046e7a6d12cc87982422efa41b31">worksheet_write_string</a> (worksheet, row + 1, col,     <span class="stringliteral">&quot;Total&quot;</span>,       bold);</div>
<div class="line">    <a class="code" href="worksheet_8h.html#ae57117f04c82bef29805ec3eabc219bb">worksheet_write_formula</a>(worksheet, row + 1, col + 1, <span class="stringliteral">&quot;=SUM(B2:B5)&quot;</span>, money);</div>
</div><!-- fragment --><p>In the next section we will look at handling more data types.</p>
<p>Next: <a class="el" href="tutorial03.html">Tutorial 3: Writing different types of data to the XLSX File</a>. </p>
</div></div><!-- contents -->
</div><!-- PageDoc -->
<div class="ttc" id="aworkbook_8h_html_ad9e7aeebc0fd43562db5bcd527b2ee5e"><div class="ttname"><a href="workbook_8h.html#ad9e7aeebc0fd43562db5bcd527b2ee5e">workbook_close</a></div><div class="ttdeci">lxw_error workbook_close(lxw_workbook *workbook)</div><div class="ttdoc">Close the Workbook object and write the XLSX file.</div></div>
<div class="ttc" id="aworksheet_8h_html_ae57117f04c82bef29805ec3eabc219bb"><div class="ttname"><a href="worksheet_8h.html#ae57117f04c82bef29805ec3eabc219bb">worksheet_write_formula</a></div><div class="ttdeci">lxw_error worksheet_write_formula(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *formula, lxw_format *format)</div><div class="ttdoc">Write a formula to a worksheet cell.</div></div>
<div class="ttc" id="aworkbook_8h_html_a1cf96608a23ee4eb0e8467c15240d00b"><div class="ttname"><a href="workbook_8h.html#a1cf96608a23ee4eb0e8467c15240d00b">workbook_new</a></div><div class="ttdeci">lxw_workbook * workbook_new(const char *filename)</div><div class="ttdoc">Create a new workbook object.</div></div>
<div class="ttc" id="aformat_8h_html_a85e1b0baf44b445b65894e48722aec14"><div class="ttname"><a href="format_8h.html#a85e1b0baf44b445b65894e48722aec14">format_set_bold</a></div><div class="ttdeci">void format_set_bold(lxw_format *format)</div><div class="ttdoc">Turn on bold for the format font.</div></div>
<div class="ttc" id="aformat_8h_html_af77bbd0003344cb16d455c7fb709e16c"><div class="ttname"><a href="format_8h.html#af77bbd0003344cb16d455c7fb709e16c">format_set_num_format</a></div><div class="ttdeci">void format_set_num_format(lxw_format *format, const char *num_format)</div><div class="ttdoc">Set the number format for a cell.</div></div>
<div class="ttc" id="astructlxw__worksheet_html"><div class="ttname"><a href="structlxw__worksheet.html">lxw_worksheet</a></div><div class="ttdoc">Struct to represent an Excel worksheet.</div><div class="ttdef"><b>Definition:</b> worksheet.h:1603</div></div>
<div class="ttc" id="astructlxw__format_html"><div class="ttname"><a href="structlxw__format.html">lxw_format</a></div><div class="ttdoc">Struct to represent the formatting properties of an Excel format.</div><div class="ttdef"><b>Definition:</b> format.h:358</div></div>
<div class="ttc" id="astructlxw__workbook_html"><div class="ttname"><a href="structlxw__workbook.html">lxw_workbook</a></div><div class="ttdoc">Struct to represent an Excel workbook.</div><div class="ttdef"><b>Definition:</b> workbook.h:273</div></div>
<div class="ttc" id="aworksheet_8h_html_ac208046e7a6d12cc87982422efa41b31"><div class="ttname"><a href="worksheet_8h.html#ac208046e7a6d12cc87982422efa41b31">worksheet_write_string</a></div><div class="ttdeci">lxw_error worksheet_write_string(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *string, lxw_format *format)</div><div class="ttdoc">Write a string to a worksheet cell.</div></div>
<div class="ttc" id="aworksheet_8h_html_ad9fc47d3beaa2ab4759414e8580c2289"><div class="ttname"><a href="worksheet_8h.html#ad9fc47d3beaa2ab4759414e8580c2289">worksheet_write_number</a></div><div class="ttdeci">lxw_error worksheet_write_number(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, double number, lxw_format *format)</div><div class="ttdoc">Write a number to a worksheet cell.</div></div>
<div class="ttc" id="aworkbook_8h_html_a81d456b4f65a464e78e4a0030ecc3c2e"><div class="ttname"><a href="workbook_8h.html#a81d456b4f65a464e78e4a0030ecc3c2e">workbook_add_worksheet</a></div><div class="ttdeci">lxw_worksheet * workbook_add_worksheet(lxw_workbook *workbook, const char *sheetname)</div><div class="ttdoc">Add a new worksheet to a workbook.</div></div>
<div class="ttc" id="aworkbook_8h_html_a279a5d7075d09a3931aae9782afede33"><div class="ttname"><a href="workbook_8h.html#a279a5d7075d09a3931aae9782afede33">workbook_add_format</a></div><div class="ttdeci">lxw_format * workbook_add_format(lxw_workbook *workbook)</div><div class="ttdoc">Create a new Format object to formats cells in worksheets.</div></div>
<!-- HTML footer for doxygen 1.8.20-->
<!-- start footer part -->
<hr class="footer"/><address class="footer"><small>
Copyright 2014-2020 John McNamara.
Generated by&#160;<a href="http://www.doxygen.org/index.html"><img class="footer" src="doxygen.svg" width="104" height="31" alt="doxygen"/></a> 1.8.20
</small></address>
</body>
</html>
